<?php

class ugp{

	static $pDB;

	/**
	 * Constructor for phylobyte Users and Groups class.
	 **/
	function __construct(){
		self::$pDB = $GLOBALS['PHYLOBYTEDB'];
	}

	/**
	 * Add or update a group. if an ID is provided and the group exists it will be updated.
	 * @param Array groupArray with id, name, description
	 * @return boolean
	 **/
	function group_put($groupArray){
		//take in a group array, write it to the database
		//use replace, so it will overwrite if an ID is provided

		//make sure we don't change the admin group
		if(isset($groupArray['id']) && $groupArray['id'] == 1){
			phylobyte::messageAddAlert('Please note that "admin" is a special group that you can not rename or delete.');
			$groupArray['name'] = 'admin';
		}

		if(!isset($groupArray['name']) || !ctype_alnum($groupArray['name'])){
			phylobyte::messageAddAlert('Group names must be alphanumeric and not empty.');
			return false;
		}

		$name = self::$pDB->quote($groupArray['name']);
		$id = isset($groupArray['id']) ? self::$pDB->quote($groupArray['id']) : 'NULL'; // Handle null for ID
		//check if the group exists
		$group = self::$pDB->prepare("
			SELECT name
			FROM p_groups WHERE name=$name AND id<>$id;");
		$group->execute();
		$group = $group->fetchAll();

		if(count($group) >= 1){
			phylobyte::messageAddError('Failed to update groups.');
			phylobyte::messageAddAlert('A group with that name already exists.');
			return false;
		}

		$description = isset($groupArray['description']) ? self::$pDB->quote($groupArray['description']) : 'NULL'; // Handle null for description
		$optional = (isset($groupArray['id']) && $groupArray['id'] != '' ? 'id,' : '');
		$id_value_for_insert = (isset($groupArray['id']) && $groupArray['id'] != '' ? self::$pDB->quote($groupArray['id']).',' : '');
		$query = self::$pDB->prepare("
			REPLACE INTO p_groups ($optional name, description )
			VALUES ($id_value_for_insert $name, $description ); ");
		if($query->execute()){
			phylobyte::messageAddNotification('Successfully updated groups.');
			return true;
		}else{
			phylobyte::messageAddError('Error updating groups.');
			return false;
		}
	}

	/**
	 * Add or update a user. if an ID is provided and the user exists it will be updated.
	 * @param Array userArray with attributes
	 * @return boolean
	 **/
	function  user_put($userArray){

		//if no user id is supplied, we have a certain set of requirements
		//a user name has to be provided, and either a password or autopass
		function genRandomString($source, $length) {
			$string = '';
			for ($p = 0; $p < $length; $p++) {
				$string .= $source[mt_rand(0, strlen($source) - 1)];
			}
			return $string;
		}

		if(isset($userArray['autopass']) && $userArray['autopass'] == true){
			$userArray['password'] = genRandomString('aaabcdeeefgghhiiijkllmnnooopqrrssttuuuvwxyyz', 6).genRandomString('0123456789', 3);
			phylobyte::messageAddAlert('Generated password: '.$userArray['password']);
		}

		if(!isset($userArray['id']) || $userArray['id'] == null){
			if(!isset($userArray['username']) || $userArray['username'] == null || !isset($userArray['password']) || strlen($userArray['password']) < 4){
				phylobyte::messageAddError('You need to provide at least a user name and password to create a new user.');
				return false;
			}
		}

		//now, regardless of whether a user id is supplied, we need to make sure
		//the user name is available
		$userNameArray = $this->user_get(isset($userArray['username']) ? $userArray['username'] : null);
		if(!empty($userNameArray) && ( !isset($userArray['id']) || $userNameArray[0]['id'] != $userArray['id']) ){ // Fix: Check if $userNameArray is not empty
			phylobyte::messageAddError('A user with that name already exists.');
			return false;
		}

		//fail if invalid email
		include_once('../plugins/EmailAddressValidator.php');
		$validator = new EmailAddressValidator;
		if (isset($userArray['email']) && !$validator->check_email_address($userArray['email']) && $userArray['email'] != null) {
			phylobyte::messageAddError('That is not a valid email address.');
			return false;
		}

		//write changes to database, keepind in mind:
		//if the password is null, don't touch it
		//if the user ID is null, don't privide it
		//id, description, passwordhash are optional

		$optionalKeys = ''; // Fix: Initialize variable
		$optionalVals = ''; // Fix: Initialize variable
		$optionalUpdate = '';

		if(isset($userArray['id']) && $userArray['id'] != null){
			$optionalKeys.= 'id,';
			$optionalVals.= self::$pDB->quote($userArray['id']).',';
		}
		if(isset($userArray['password']) && trim($userArray['password']) != ''){
			$optionalKeys.= 'passwordhash,';
			$quotedPassword = self::$pDB->quote(sha1($userArray['password']));
			$optionalVals.= $quotedPassword.',';
			$optionalUpdate = "passwordhash=$quotedPassword,";
		}

		$username = isset($userArray['username']) ? self::$pDB->quote($userArray['username']) : 'NULL';
		$status = isset($userArray['status']) ? self::$pDB->quote($userArray['status']) : 'NULL';
		$email = isset($userArray['email']) ? self::$pDB->quote($userArray['email']) : 'NULL';
		$name = isset($userArray['name']) ? self::$pDB->quote($userArray['name']) : 'NULL';

		// Trim trailing commas from optionalKeys and optionalVals
		$optionalKeys = rtrim($optionalKeys, ',');
		$optionalVals = rtrim($optionalVals, ',');
		$optionalUpdate = rtrim($optionalUpdate, ',');

		// Construct the INSERT part of the query
		$insertColumns = ($optionalKeys != '' ? $optionalKeys . ',' : '') . 'username, status, email, name';
		$insertValues = ($optionalVals != '' ? $optionalVals . ',' : '') . "$username, $status, $email, $name";

		// Construct the UPDATE part of the query
		$updateSet = '';
		if ($optionalUpdate != '') {
			$updateSet .= $optionalUpdate . ',';
		}
		$updateSet .= "name=$name, email=$email, status=$status, username=$username";
		$updateSet = rtrim($updateSet, ','); // Ensure no trailing comma

		$query = "
			INSERT INTO p_users ($insertColumns)
			VALUES ($insertValues)
			ON CONFLICT(id) DO UPDATE SET
			$updateSet;
			";
		// Note: Changed to ON CONFLICT(id) DO UPDATE SET for SQLite compatibility,
		// assuming the database might be SQLite based on the error message.
		// If it's MySQL, it should be ON DUPLICATE KEY UPDATE.
		// The original error "near "DUPLICATE"" suggests the syntax itself was the issue,
		// which is common when mixing database specific syntax or having malformed clauses.
		// If 'id' is not the primary key or unique key, this ON CONFLICT will not work as expected.
		// Assuming 'id' is the primary key for ON CONFLICT.

		$query = self::$pDB->prepare($query);
		if($query->execute()){
			phylobyte::messageAddNotification('Successfully updated user.');
			return true;
		}else{
			phylobyte::messageAddError('Error updating user.');
			return false;
		}
	}

	function user_getattrs($uid, $gid){

		//return false if the user is NOT a member of that group
		$userid = self::$pDB->quote($uid);
		$groupid = self::$pDB->quote($gid);

		$checkResults = self::$pDB->prepare("
			SELECT id
			FROM p_memberships WHERE userid=$userid AND groupid=$groupid;");
		$checkResults->execute();
		$checkResults = $checkResults->fetchAll(PDO::FETCH_ASSOC);

		if(count($checkResults) > 0){
			//great! the user is a member of the group, so let's get the attrs
			$getAttrs = self::$pDB->prepare("
			SELECT p_gattributes.*,
			(SELECT value FROM p_uattributes WHERE uid=$userid AND p_uattributes.aid = p_gattributes.id) AS value
			FROM p_gattributes WHERE p_gattributes.gid=$groupid
			");
			$getAttrs->execute();
			$getAttrs = $getAttrs->fetchAll(PDO::FETCH_ASSOC);
			return $getAttrs;
		}else{
			return false;
		}

	}

	function user_putattr($uid, $aid, $value){
	
		$userid = self::$pDB->quote($uid);
		$attrid = self::$pDB->quote($aid);

		if($value == null || $value == ''){
			self::$pDB->exec("
			DELETE FROM p_uattributes
			WHERE uid=$userid AND aid=$attrid;
			");
			phylobyte::messageAddNotification('Cleared attribute from database.');
			return true;
		}

		$value = self::$pDB->quote($value);

		//value is not null, so insert or update

		$checkResults = self::$pDB->prepare("
			SELECT id
			FROM p_uattributes
			WHERE uid=$userid AND aid=$attrid;
		");
		$checkResults->execute();
		$checkResults = $checkResults->fetchAll();

		if(count($checkResults) > 0){
			//update
			if(self::$pDB->exec("
			UPDATE p_uattributes
			SET value=$value WHERE
			uid=$userid AND aid=$attrid;
			")){
			phylobyte::messageAddNotification('Successfully updated attribute.');
			}else{
			phylobyte::messageAddError('There was a problem updating the attribute.');
			}
			return true;
		}else {
		    //insert
		    if(self::$pDB->exec("
		    INSERT INTO p_uattributes (uid, aid, value)
		    VALUES ($userid, $attrid, $value);
		    ")){
			phylobyte::messageAddNotification('Successfully added attribute.');
		    }else {
		    phylobyte::messageAddError('There was a problem adding the attribute.');
		    }
		    return true;
		}
	}

	function user_formatattr($attributeArray, $attributeTemplate){

	}

	function membership_add($uid, $gid){

		$userid = self::$pDB->quote($uid);
		$groupid = self::$pDB->quote($gid);

		$checkResults = self::$pDB->prepare("
			SELECT id
			FROM p_memberships WHERE userid=$userid AND groupid=$groupid;");
		$checkResults->execute();
		$checkResults = $checkResults->fetchAll();

		if(count($checkResults) > 0){
			phylobyte::messageAddError('The user is already a member of that group.');
		}else{
			if(
			self::$pDB->exec("
				INSERT INTO p_memberships (userid, groupid) VALUES ($userid, $groupid);
			") !== false
			){
			phylobyte::messageAddNotification('Successfully added or updated membership.');

			}else{
			phylobyte::messageAddError('There was a problem adding the user to a group');
			}
		}


	}

	/**
	 * Delete a membership.
	 * @param int membershipId
	 * @return boolean
	 * TODO make sure to delete any attributes as well
	 **/
	function membership_remove($mid){
			$mid = self::$pDB->quote($mid); // Ensure $mid is quoted
			if(self::$pDB->exec("
				DELETE FROM p_memberships
				WHERE id=$mid;")
			){
			phylobyte::messageAddNotification('Successfully removed membership from group.');
			return true;
			}else{
			phylobyte::messageAddError('There was a problem deleting that membership.');
			return false;
			}
	}
	
	function membership_removeuser($userid, $groupid){
		// $mid is not defined in this function, so this line is problematic.
		// It seems like it was intended to quote $userid and $groupid, but they are already quoted in the query.
		// Removing the problematic line.
		// self::$pDB->quote($mid); 
		if(self::$pDB->exec("
			DELETE FROM p_memberships
			WHERE userid='$userid' AND groupid='$groupid';")
		){
			return true;
		}else{
			phylobyte::messageAddError('There was a problem deleting that membership.');
			return false;
		}
	}
	
	function membership_clear($userid, $groupid){
		if(self::$pDB->exec("
			DELETE attributes FROM p_uattributes AS attributes
			JOIN p_gattributes ON attributes.aid = p_gattributes.id
			WHERE attributes.uid='$userid' AND p_gattributes.gid='$groupid';") !== false
		){
			return true;
		}else{
			phylobyte::messageAddError('There was a problem clearing attributes.');
			return false;
		}
	}
	
	/**
	 * Delete a group.
	 * @param int groupID
	 * @return boolean
	 * TODO add second perameter for deleting attribute
	 **/
	function group_delete($groupID){

		$delete = $this->group_deleteable($groupID);
		if($delete === true){
			$groupID = self::$pDB->quote($groupID);
			$success = true;
			if(!self::$pDB->exec("
				DELETE FROM p_uattributes
				WHERE aid IN (
					SELECT id FROM p_gattributes
					WHERE gid=$groupID
				);")){phylobyte::messageAddAlert('No user attributes to clear.');}
			if(!self::$pDB->exec("
				DELETE FROM p_gattributes
				WHERE gid=$groupID;")){phylobyte::messageAddAlert('No group attributes to clear.');}
			if($success){ phylobyte::messageAddNotification('Successfully deleted group.'); }
			if(!self::$pDB->exec("
				DELETE FROM p_groups
				WHERE id=$groupID;")){$success = false; phylobyte::messageAddError('Error deleting group.');}
		}else{
			phylobyte::messageAddError('The group '.$groupID.' could not be deleted: '.$delete);
		}

	}

	/**
	 * Add or delete a user by ID.
	 * @param Int userID
	 * @return boolean
	 **/
	function user_delete($userID){
		//check deleteable
		if($this->user_deleteable($userID)){
			self::$pDB->exec("DELETE FROM p_uattributes WHERE uid={$_POST['u_uid']};");
			self::$pDB->exec("DELETE FROM p_memberships WHERE p_memberships.userid={$_POST['u_uid']};");
			self::$pDB->exec("DELETE FROM p_users WHERE id={$_POST['u_uid']};");
			phylobyte::messageAddNotification('Successfully deleted user.');
			return true;
		}
		return false;
	}

	function group_deleteable($groupID){
		//return TRUE if safe to delete
		//otherwise, return the error

		$group = $this->group_get($groupID);
		if(isset($group[0]['id']) && $group[0]['id'] == 1){
			return 'You can not delete the Admin group.';
		}elseif(isset($group[0]['members']) && $group[0]['members'] != 0){
			return 'You can not delete a group that still has members.';
		}elseif(isset($group[0]['id']) && $group[0]['id'] == ''){
			return 'The group you are trying to delete does not exist.';
		}else{
			return true;
		}

	}

	function user_deleteable($userID){

		$result = true;
		//first, you can't delete yourself

		if(isset($_SESSION['loginid']) && $_SESSION['loginid'] == $userID){
			phylobyte::messageAddError('You can not delete yourself.');
			$result = false;
		}
		//next, you can't delete the last admin (this also ensures there is at least one user left)
		$tryDeleteUser = $this->user_get($userID);
		if(!empty($tryDeleteUser) && isset($tryDeleteUser[0]['primarygroup']) && $tryDeleteUser[0]['primarygroup'] == '1' && $this->group_format($this->group_get($tryDeleteUser[0]['primarygroup']), '%m%') == '1'){
			phylobyte::messageAddError('You can not delete the last administrator.');
			$result = false;
		}

		return $result;
	}

	/**
	 * Retrieve a group and its information from the database.
	 * @param String groupID pass null and a filter to search groups
	 * @return Array
	 **/
	function group_get($groupID, $groupsFilter = '', $user = false, $reverse = false, $strict = false){

		// The $user parameter can be a boolean 'false' when no user is specified.
		// ctype_digit() expects a string, and passing a boolean directly is deprecated in PHP 8.1+.
		// Ensure $user is a string representation of a digit or a non-negative integer.
		$is_user_valid_id = false;
		$user_id_value = $user; // Use this for quoting later

		if (is_bool($user)) {
			// If $user is boolean, convert true to 1, false to 0.
			// Only treat 'true' (which becomes 1) as a valid ID in this context.
			if ($user === true) {
				$user_id_value = 1;
				$is_user_valid_id = true;
			} else { // $user is false
				$user_id_value = 0; // Not a valid ID for this branch
				$is_user_valid_id = false;
			}
		} elseif (is_int($user) && $user >= 0) {
			$is_user_valid_id = true;
		} elseif (is_string($user) && ctype_digit($user)) {
			$is_user_valid_id = true;
		}

		if($is_user_valid_id){
			//this is a little different; we need to get only the groups a user is a member of
			$userid = self::$pDB->quote($user_id_value);
			if($reverse == false){
				$groups = self::$pDB->prepare("
					SELECT p_memberships.id AS mid, p_groups.*, (
							SELECT COUNT(*)
							FROM p_memberships
							WHERE groupid=p_groups.id
							) AS members
					FROM p_memberships
					JOIN p_groups ON p_memberships.groupid = p_groups.id
					WHERE userid=$userid;
				");
			}else{
				$groups = self::$pDB->prepare("
					SELECT DISTINCT * FROM p_groups WHERE NOT EXISTS
					(SELECT * FROM p_memberships WHERE p_memberships.groupid = p_groups.id
					AND p_memberships.userid = $userid)
				");
			}

			$groups->execute();
			$groups = $groups->fetchAll();
			return $groups;	
		}else{
			//if gruoupID, return array, otherwise return multiple array
			if($groupID != null){
				$group = self::$pDB->prepare("
					SELECT *, (
						SELECT COUNT(*)
						FROM p_memberships
						WHERE groupid=p_groups.id
						) as members
					FROM p_groups WHERE id=$groupID ORDER BY name;");
				$group->execute();
				$group = $group->fetchAll();
				return $group;
			}else{
				if(!$strict){
				$groups = self::$pDB->prepare("
					SELECT *, (
						SELECT COUNT(*)
						FROM p_memberships
						WHERE groupid = p_groups.id
						) as members
					FROM p_groups
					WHERE name LIKE '%$groupsFilter%' ORDER BY name;");
				}else{
				$groups = self::$pDB->prepare("
					SELECT *, (
						SELECT COUNT(*)
						FROM p_memberships
						WHERE groupid = p_groups.id
						) as members
					FROM p_groups
					WHERE name = '$groupsFilter' ORDER BY name;");
				}
				$groups->execute();
				$groups = $groups->fetchAll();
				return $groups;
			}
		}

	}
	
	function group_exists($groupName){
		$groupName = self::$pDB->quote($groupName);
		$groupQuery = self::$pDB->prepare("
			SELECT id FROM p_groups WHERE name=$groupName;
		");
		$groupQuery->execute();
		return $groupQuery->fetchAll(PDO::FETCH_ASSOC);
	}

	/**
	 * Retrieve a user and its information from the database.
	 * @param String userID otherwise pass null and a filter to search users
	 * @return Array
	 * TODO include the user's attributeArray, key is attribute group, value is attributeArray
	 **/
	function user_get($userID, $filter = '', $limit = 100, $orderBy = 'username'){
		//return array or multiple arrays

		//if userID, return array, otherwise return multiple array
		if($userID != null){
			if(ctype_digit($userID)){
				$user = self::$pDB->prepare("
					SELECT *
					FROM p_users WHERE id=$userID
					ORDER BY $orderBy
					LIMIT $limit;");
				$user->execute();
				$user = $user->fetchAll();
				return $user;
			}else{
				//we're looking for a user by name
				$userID = self::$pDB->quote($userID);
				$user = self::$pDB->prepare("
					SELECT *
					FROM p_users WHERE username=$userID
					ORDER BY $orderBy
					LIMIT $limit;");
				$user->execute();
				$user = $user->fetchAll();
				return $user;
			}
		}else{
			$users = self::$pDB->prepare("
				SELECT *
				FROM p_users
				WHERE username LIKE '%$filter%' OR
				email LIKE '%$filter%' OR name LIKE '%$filter%'
				ORDER BY $orderBy
				LIMIT $limit;");
			$users->execute();
			$users = $users->fetchAll();
			return $users;
		}

	}

	/**
	 * Format an array of group information, use with group_get
	 * @param Array groupArray use with group_get to ensure proper format
	 * @param String formatString string with formatting markers to replace with the group information
	 * %i% = id
	 * %n% = name
	 * %d% = description
	 * %m% = members
	 * %mi% = mid
	 * @return String
	 * TODO add format string for groups attributes
	 **/
	function group_format($groupsArray, $formatString){
		//take in an array of groups, format based on the string
		// %i% = id
		// %n% = name
		// %d% = description
		// %m% = members

		$result = null;

		foreach($groupsArray as $group) {
		    $needles = array(
				'%i%',
				'%n%',
				'%d%',
				'%m%',
				'%mi%'
		    );
		    $replacements = array(
				$group['id'],
				$group['name'],
				$group['description'],
				$group['members'] ?? '', // Use null coalescing operator
				$group['mid'] ?? ''      // Use null coalescing operator
		    );
		    $result.=str_replace($needles, $replacements, $formatString);
		}

		return $result;
	}

	/**
	 * Format an array of user information, use with user_get
	 * @param Array usersArray use with user_get to ensure proper format
	 * @param String formatString string with formatting markers to replace with the group information
	 * %i% = id
	 * %u% = username
	 * @param groupFormatString
	 * @return String
	 * TODO since group_format now accepts attribute format, allow it here too
	 **/
	function user_format($usersArray, $formatString = '%i%, %u% <br/>', $groupFormatString = '%n%'){
		//take in an array of groups, format based on the string
		// %i% = id
		// %u% = username

		$result = null;

		foreach($usersArray as $userArray) {

			switch($userArray['status']){

				case 'active':
					$color = '#080';
				break;

				case 'disabled':
					$color = '#008';
					$status = '<span style="color: #008;">Disabled</span>';
				break;

				case 'suspended':
					$color = '#800';
					$status = '<span style="color: #800;">Suspended</span>';
				break;

				case 'flagged':
					$color = '#540';
					$status = '<span style="color: #540;">Flagged</span>';
				break;

				default:
						$color = '#444';
					if(ctype_digit($userArray['status'])){
						$color = '#080';
						$userArray['status'] = 'Reserved';
					}else{
						$userArray['status'] = ucfirst($userArray['status']);
					}
			}

		    $needles = array(
				'%i%',
				'%u%',
				'%n%',
				'%e%',
				'%s%',
				'%sC%'
		    );
		    $replacements = array(
				$userArray['id'],
				$userArray['username'],
				$userArray['name'],
				$userArray['email'],
				$userArray['status'],
				$color
		    );

		    $result.=str_replace($needles, $replacements, $formatString);
		}

		return $result;
	}

	function group_attributeAdd($gid, $attribute, $default){
		if(!ctype_alnum($attribute)){
			phylobyte::messageAddAlert('Attribute names must be alphanumeric.');
			return false;
		}

		$attribute = self::$pDB->quote($attribute);
		$gid = self::$pDB->quote($gid);
		//check if the attribute exists
		$group = self::$pDB->prepare("
			SELECT id
			FROM p_gattributes WHERE gid=$gid AND attribute=$attribute;");
		$group->execute();
		$group = $group->fetchAll();

		if(count($group) >= 1){
			phylobyte::messageAddError('Failed to update group.');
			phylobyte::messageAddAlert('An attribute of that name already exists within this group.');
			return false;
		}

		$default = self::$pDB->quote($default);
		$query = self::$pDB->prepare("
			INSERT INTO p_gattributes (gid, attribute, defaultvalue)
			VALUES ($gid, $attribute, $default); ");
		if($query->execute()){
			phylobyte::messageAddNotification('Successfully added attribute.');
			return true;
		}else{
			phylobyte::messageAddError('Error updating group.');
			return false;
		}
	}

	/**
	 * Retrieve an attribute and its information from the database.
	 * @param gid=String/Integer groupID, null returns all
	 * @param filter=null/String filter attributes by name and default value (union), if true, return by attribute ID, false, delete
	 * @return Array
	 **/
	function group_attributesGet($gid, $filter = ''){

		if($filter === false){
			//if this is the case, $gid is actually the attribute id
			$id = self::$pDB->quote($gid);
			$query = self::$pDB->prepare("
			DELETE FROM p_gattributes WHERE id=$id;
			");
			$query2 = self::$pDB->prepare("
			DELETE FROM p_uattributes WHERE aid=$id;
			");
			if($query->execute() && $query2->execute()){
				phylobyte::messageAddNotification('Successfully deleted attribute.');
				return true;
			}else{
				phylobyte::messageAddError('Error deleting attribute.');
				return false;
			}
		}else{
			$gid = self::$pDB->quote($gid);
			$filter = self::$pDB->quote('%'.$filter.'%');
			$attributes = self::$pDB->prepare("
				SELECT *
				FROM p_gattributes WHERE gid=$gid AND attribute LIKE $filter OR gid=$gid AND defaultvalue LIKE $filter;");
			$attributes->execute();
			$attributes = $attributes->fetchAll();

			return $attributes;
		}
		
	}

	/**
	 * Format an array of attribute information, use with group_attributesGet()
	 * @param attributeArray=Array attributesArray use with group_attributesGet() to ensure proper format
	 * @param formatTemplage=String string with formatting markers to replace with the attribute information
	 * %i% = id
	 * %g% = groupId
	 * %G% = groupName (not yet implemented)
	 * %a% = attribute
	 * %d% = default
	 * @return String
	 **/
	function group_attributesFormat($attributeArray, $template){
	
		$result = null;

		foreach($attributeArray as $attribute) {
		    $needles = array(
				'%i%',
				'%g%',
				'%a%',
				'%d%'
		    );
		    $replacements = array(
				$attribute['id'],
				$attribute['gid'],
				$attribute['attribute'],
				$attribute['defaultvalue']
		    );
		    $result.=str_replace($needles, $replacements, $template);
		}

		return $result;
	}
	
	function getDatabase(){
		// Check if the PDO connection is SQLite
		if (self::$pDB->getAttribute(PDO::ATTR_DRIVER_NAME) == 'sqlite') {
			// For SQLite, the database name isn't directly queryable with DATABASE().
			// Return a generic identifier.
			return 'sqlite_database'; // Placeholder
		} else {
			// Assume MySQL or other database where DATABASE() works
			$query = "SELECT DATABASE();";
			$query = self::$pDB->prepare($query);
			$query->execute();
			$results = $query->fetchAll(PDO::FETCH_ASSOC);
			// Ensure the key exists before accessing
			return $results[0]['DATABASE()'] ?? 'unknown_database';
		}
	}
	
	
	/**
	 * Check Database
	 * @param $template=String
	 * @param $table=String
	 * @param $arrColumnsExpected=Array
	 * @param $title=String
	 * @param $description=String
	 * @param $good=String
	 * @param $bad=String
	 **/
	function checkDb($template = null, $table = null, $arrColumnsExpected = null,
		$title = 'Table', $description = 'Table Stats',
		$good = 'y', $bad = 'n'){
		
		$statusElements = Array(
			'table' => $table,
			'tableExists' => false,
			'title' => $title,
			'description' => $description,
			'arrColumnsExpected' => $arrColumnsExpected,
			'colsExpected' => null,
			'arrColsFound' => null,
			'colsFound' => null,
			'colsMatch' => false,
			'dataExists' => false,
			'dataCount' => 0
		);
		
		// Determine database driver
		$driver = self::$pDB->getAttribute(PDO::ATTR_DRIVER_NAME);

		// Check table existence
		if ($driver == 'sqlite') {
			$checkTableQuery = "SELECT name FROM sqlite_master WHERE type='table' AND name=:table_name;";
		} else { // Assume MySQL
			$checkTableQuery = "SHOW TABLES LIKE :table_name;";
		}
		$checkTableQuery = self::$pDB->prepare($checkTableQuery);
		$checkTableQuery->bindParam(':table_name', $table);
		$checkTableQuery->execute();
		$checkTableQueryResults = $checkTableQuery->fetchAll(PDO::FETCH_ASSOC);
		
		if(count($checkTableQueryResults) > 0){ // Check count of results, not count of first result
			$statusElements['tableExists'] = true;
		}
		
		// Get row count
		// Only attempt if table exists to avoid errors
		if ($statusElements['tableExists']) {
			$checkRowsQuery = "SELECT COUNT(*) FROM $table;"; // Changed to COUNT(*)
			$checkRowsQuery = self::$pDB->prepare($checkRowsQuery);
			$checkRowsQuery->execute();
			$checkRowsQueryResults = $checkRowsQuery->fetchAll();
			
			if(isset($checkRowsQueryResults[0][0])){ // Check if result exists
				$statusElements['dataExists'] = true;
				$statusElements['dataCount'] = $checkRowsQueryResults[0][0];
			}
		}
		
		// Column check
		$stringColsExpected = ''; // Initialize as empty string
		if(is_array($statusElements['arrColumnsExpected']) && count($statusElements['arrColumnsExpected']) > 0){ // Added is_array check
			foreach($statusElements['arrColumnsExpected'] as $value) {
				$stringColsExpected.=$value.', ';
			}
		}
		$statusElements['colsExpected'] = ($stringColsExpected !== '') ? substr($stringColsExpected, 0, -2) : '';
		
		$statusElements['arrColsFound'] = []; // Initialize to an empty array
		
		if ($statusElements['tableExists']) { // Only check columns if table exists
			if ($driver == 'sqlite') {
				$checkColumnsQuery = "PRAGMA table_info($table);";
			} else { // Assume MySQL
				$checkColumnsQuery = "
				SELECT COLUMN_NAME 
				FROM INFORMATION_SCHEMA.COLUMNS 
				WHERE TABLE_SCHEMA='{$this->getDatabase()}' 
					AND TABLE_NAME='$table'
				";
			}
			$checkColumnsQuery = self::$pDB->prepare($checkColumnsQuery);
			$checkColumnsQuery->execute();
			$checkColumnsResults = $checkColumnsQuery->fetchAll(PDO::FETCH_ASSOC);
			
			foreach($checkColumnsResults as $key => $column) {
				if ($driver == 'sqlite') {
					$statusElements['arrColsFound'][] = $column['name']; // SQLite uses 'name' for column name
				} else {
					$statusElements['arrColsFound'][] = $column['COLUMN_NAME'];
				}
			}
		}

		$stringColsFound = ''; // Initialize as empty string
		if(is_array($statusElements['arrColsFound']) && count($statusElements['arrColsFound']) > 0){ // Added is_array check
			foreach($statusElements['arrColsFound'] as $value) {
				$stringColsFound.=$value.', ';
			}
		}
		$statusElements['colsFound'] = ($stringColsFound !== '') ? substr($stringColsFound, 0, -2) : '';
		
		if($statusElements['arrColumnsExpected'] != null && $statusElements['arrColsFound'] != null){
			if(
			count(array_diff($statusElements['arrColumnsExpected'], $statusElements['arrColsFound'])) == 0 &&
			count(array_diff($statusElements['arrColsFound'], $statusElements['arrColumnsExpected'])) == 0
			){
				$statusElements['colsMatch'] = true;
			}
		}
		
		if($template == null){
			return(print_r($statusElements, true));
		}else{
		
			if($statusElements['tableExists']){
				$statusElements['tableExists'] = $good;
			}else{
				$statusElements['tableExists'] = $bad;
			}
			
			if($statusElements['dataExists']){
				$statusElements['dataExists'] = $good;
			}else{
				$statusElements['dataExists'] = $bad;
			}
			
			if($statusElements['colsMatch']){
				$statusElements['colsMatch'] = $good;
			}else{
				$statusElements['colsMatch'] = $bad;
			}
		
			// Define the placeholders and their corresponding keys in $statusElements
			$placeholder_map = [
				'%t%' => 'table',
				'%tE%' => 'tableExists',
				'%T%' => 'title',
				'%D%' => 'description',
				'%cE%' => 'colsExpected',
				'%cF%' => 'colsFound',
				'%cM%' => 'colsMatch',
				'%dE%' => 'dataExists',
				'%dC%' => 'dataCount'
			];

			$needles = [];
			$replacements = [];
			foreach ($placeholder_map as $placeholder => $key_in_statusElements) {
				$needles[] = $placeholder;
				// Ensure the value exists in $statusElements, provide empty string if not
				$replacements[] = $statusElements[$key_in_statusElements] ?? '';
			}

			return str_replace($needles, $replacements, $template);
		}
	}

}

$GLOBALS['UGP'] = new ugp;

?>